package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import bean.contents;


/**
*	数据库连接 工具 
*
*/
public class Conn {

	//测试
	public static void main(String[] args) {
//		getConn();
		try {
			addContens("你好");
			queryContens("");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//获取数据库连接对象
	public static Connection getConn() throws ClassNotFoundException, SQLException {
		Connection conn=null;
		String url="jdbc:mysql://localhost:3306/jqueryui?characterEncoding=UTF-8";//设置数据库编码
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(url, "root", "123456");
		return conn;
	}
	
	//新增留言
	public static contents addContens(String content) throws SQLException, ClassNotFoundException{
		contents c = null;
		Connection conn = getConn();
		if(null != conn){
			String sql = "insert into contents(content) values(?)";
			System.out.println(sql);
			PreparedStatement pstat = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			
			pstat.setString(1,content);
			pstat.execute();
			
			ResultSet rs =  pstat.getGeneratedKeys(); 
			if(rs.next()){
				c = new contents();
				c.setId(Integer.parseInt(rs.getString(1)));//得到ID
				c.setContent("");
			}
			System.out.println("成功插入一条记录");
			
			pstat.close();
		    conn.close();
		}
		return c;
	}
	
	
	//查询留言留言
	public static List<contents> queryContens(String lastId) throws ClassNotFoundException, SQLException{
		Connection conn = getConn();
		List<contents> l = new ArrayList<contents>();
		if(null != conn){
			String sql = " SELECT *  FROM contents WHERE 1=1";
			if(null != lastId && lastId.length() > 0){
				sql += " and id <"+lastId+" ";//首次拉取数据lastId 为空
			}
			sql += "  ORDER BY id DESC LIMIT 5  ";
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			//处理结果
	        while(rs.next()){
	            //每次循环，都取出一条记录
	        	contents c  = new contents();
	        	c.setId(rs.getInt("id"));
	        	c.setContent(rs.getString("content"));
	        	System.out.println("id:"+c.getId()+"  content:"+c.getContent());
	        	l.add(c);//放入list
	        }
	        
	        rs.close();
	        st.close();
	        conn.close();

		}
		return l;
	}
	
}
